﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using WinNemoLib;
using System.Data.SqlClient;
using System.Text;
using System.Data;

namespace ISODOC.Models
{
    public class Request
    {
        public class DARDOC_DS
        {
            public string _Docno { get; set; }
            public string _Status { get; set; }
            public string _Empno { get; set; }
            public string _Empname { get; set; }
            public string _Empemail { get; set; }
            public string _Dep { get; set; }
            public string _Mgrname { get; set; }
            public string _Mgremail { get; set; }
            public string _DOCType { get; set; }
            public string _DOCTypenm { get; set; }
            public string _OPTType { get; set; }
            public string _OPTTypenm { get; set; }
            public string _Filename { get; set; }
            public string _FileTemp { get; set; }
            public string _Path { get; set; }
            public string _Text { get; set; }
            public string _Rev { get; set; }
            public string _Refrev { get; set; }
            public string _Refdocid { get; set; }
            public string _Size { get; set; }
            public string _Pattern { get; set; }
            public string _IssueDT { get; set; }
            public string _ExpDT { get; set; }
            public string _SOURCE { get; set; }
            public string _ISOCHECK { get; set; }
        }

        //select [dbo].[Get_DARDOC]( right(year(getdate()),2))  from DARDOC
        public string GET_DARDOC()
        {
            string ConnectionString = WinNemoLib.Nemo.GetConfiguredDbConnectionWrappertostring("Database");

            SqlConnection cnn = new SqlConnection(ConnectionString);
            StringBuilder str = new StringBuilder();
            string years = DateTime.Now.ToString("yy", new System.Globalization.CultureInfo("en-US"));
            str.AppendLine(" select isnull((max(convert(int,darno)) + 1 ),'" + years + "000001')  docno from " + WinNemoLib.Nemo.GetINI("Dbase", "Base") + ".dbo.DARDOC where darno like '" + years + "%'");

            SqlCommand cmd = new SqlCommand(str.ToString(), cnn);
            cmd.Connection.Open();
            cmd.CommandType = CommandType.Text;


            SqlDataAdapter my = new SqlDataAdapter();
            DataSet ds = new DataSet();
            my.SelectCommand = cmd;
            my.Fill(ds);

            if (ds.Tables[0].Rows.Count <= 0)
            {
                cmd.Connection.Close();
                return years + "000001";
            }
            return ds.Tables[0].Rows[0]["docno"].ToString();
        }

        public string Ins_DARDOC(ConnectionWrapper cnn, DARDOC_DS incoming)
        {
            try
            {
                incoming._Docno = GET_DARDOC();

                StringBuilder comstr = new StringBuilder();
                comstr.AppendLine(" INSERT INTO ");
                comstr.AppendLine(WinNemoLib.Nemo.GetINI("Dbase", "Base") + ".[dbo].[DARDOC]");
                comstr.AppendLine(" ([DARNO], [EMPNO], [EMPNAME],[STATUS], [EMP_EMAIL], [DEPT], [MGRNAME], [MGR_EMAIL], [DOCTYPE], [OPTTYPE], [TEMPFILE], [FILENAME], [PATHFILE], [TEXT], [REFREV]) VALUES (");
                comstr.AppendLine(" @DARNO, @EMPNO, @EMPNAME,@STATUS, @EMP_EMAIL, @DEPT, @MGRNAME, @MGR_EMAIL, @DOCTYPE, @OPTTYPE, @TEMPFILE, @FILENAME, @PATHFILE, @TEXT, @REFREV) ");

                CommandWrapper cmd = cnn.CreateCommand();
                cmd.CommandText = comstr.ToString();

                cmd.AddParameterStringWithValue("@DARNO", incoming._Docno.ToString());
                cmd.AddParameterStringWithValue("@EMPNO", incoming._Empno.ToUpper());
                cmd.AddParameterStringWithValue("@EMPNAME", incoming._Empname.ToUpper());
                cmd.AddParameterStringWithValue("@EMP_EMAIL", incoming._Empemail.ToUpper());
                cmd.AddParameterStringWithValue("@DEPT", incoming._Dep);
                cmd.AddParameterStringWithValue("@MGRNAME", incoming._Mgrname);
                cmd.AddParameterStringWithValue("@MGR_EMAIL", incoming._Mgremail);
                cmd.AddParameterStringWithValue("@DOCTYPE", incoming._DOCType);
                cmd.AddParameterStringWithValue("@OPTTYPE", incoming._OPTType);
                cmd.AddParameterStringWithValue("@TEMPFILE", incoming._FileTemp);
                cmd.AddParameterStringWithValue("@FILENAME", incoming._Filename);
                cmd.AddParameterStringWithValue("@PATHFILE", incoming._Path);
                cmd.AddParameterStringWithValue("@TEXT", incoming._Text);
                cmd.AddParameterStringWithValue("@REFREV", incoming._Refrev);
                cmd.AddParameterStringWithValue("@STATUS", incoming._Status);

                if (cmd.ExecuteNonQuery() <= 0)
                {
                    cnn.RollbackTransaction();
                    return "";
                }

                return incoming._Docno;
            }
            catch (Exception ex)
            {
                return "";
                throw ex;
            }

        }

        public Boolean UPD_DARDOC(ConnectionWrapper cnn, DARDOC_DS incoming)
        {
            try
            {
           

                StringBuilder comstr = new StringBuilder();
                comstr.AppendLine(" UPDATE");
                comstr.AppendLine(WinNemoLib.Nemo.GetINI("Dbase", "Base") + ".[dbo].[DARDOC]");
                comstr.AppendLine(" SET [STATUS]=@STATUS, [TEMPFILE]= @TEMPFILE, [FILENAME]= @FILENAME, [PATHFILE]= @PATHFILE, [TEXT]= @TEXT, [REFREV]= @REFREV ");
                comstr.AppendLine(" WHERE [DARNO]=@DARNO");
                CommandWrapper cmd = cnn.CreateCommand();
                cmd.CommandText = comstr.ToString();

                cmd.AddParameterStringWithValue("@DARNO", incoming._Docno.ToString());
                cmd.AddParameterStringWithValue("@TEMPFILE", incoming._FileTemp);
                cmd.AddParameterStringWithValue("@FILENAME", incoming._Filename);
                cmd.AddParameterStringWithValue("@PATHFILE", incoming._Path);
                cmd.AddParameterStringWithValue("@TEXT", incoming._Text);
                cmd.AddParameterStringWithValue("@REFREV", incoming._Refrev);
                cmd.AddParameterStringWithValue("@STATUS", incoming._Status);

                if (cmd.ExecuteNonQuery() <= 0)
                {
                    cnn.RollbackTransaction();
                    return false;
                }

                return true;
            }
            catch (Exception ex)
            {
                return false;
                throw ex;
            }

        }

        public bool Ins_RELATEDOC(ConnectionWrapper cnn, string darref, string darno)
        {
            try
            {
                char[] splitchar = { ',' };
                string[] arrDarref = darref.Split(splitchar);

                StringBuilder comstr = new StringBuilder();
                comstr.AppendLine(" INSERT INTO ");
                comstr.AppendLine(WinNemoLib.Nemo.GetINI("Dbase", "Base") + ".[dbo].[RELATEDOC]");
                comstr.AppendLine(" ([DARNO], [REFNO]) VALUES (");
                comstr.AppendLine(" @DARNO, @REFNO) ");
                foreach (string item in arrDarref)
                {
                    CommandWrapper cmd = cnn.CreateCommand();
                    cmd.CommandText = comstr.ToString();

                    cmd.AddParameterStringWithValue("@DARNO", darno);
                    cmd.AddParameterStringWithValue("@REFNO", item);

                    if (cmd.ExecuteNonQuery() <= 0)
                    {
                        cnn.RollbackTransaction();
                        return false;
                    }
                }

                return true;
            }
            catch (Exception ex)
            {
                return false;
                throw ex;
            }

        }

        public List<DARDOC_DS> LIST_DARDOC(string darno="")
        {
            string ConnectionString = WinNemoLib.Nemo.GetConfiguredDbConnectionWrappertostring("Database");

            SqlConnection cnn = new SqlConnection(ConnectionString);
            StringBuilder str = new StringBuilder();

            str.AppendLine(" select a.*,b.[NAME] as DOCNM ,c.[NAME] as OPTNM from " + WinNemoLib.Nemo.GetINI("Dbase", "Base") + ".dbo.DARDOC a ");
            str.AppendLine(" INNER JOIN " + WinNemoLib.Nemo.GetINI("Dbase", "Base") + ".dbo.DOCTYPE b on a.DOCTYPE=b.DOCTYPE");
            str.AppendLine(" INNER JOIN " + WinNemoLib.Nemo.GetINI("Dbase", "Base") + ".dbo.OPTTYPE c on a.OPTTYPE=c.OPTTYPE");

            if (StrUtil.IsNotEmpty(darno))
            {
                str.AppendLine(" Where a.DARNO = " + darno);
            }

            SqlCommand cmd = new SqlCommand(str.ToString() + " order by convert(int,a.darno) ", cnn);
            cmd.Connection.Open();
            cmd.CommandType = CommandType.Text;

            List<DARDOC_DS> list = new List<DARDOC_DS>();
            SqlDataAdapter my = new SqlDataAdapter();
            DataSet ds = new DataSet();
            my.SelectCommand = cmd;
            my.Fill(ds);

            if (ds.Tables[0].Rows.Count <= 0)
            {
                cmd.Connection.Close();
                return list;
            }
            DARDOC_DS model = new DARDOC_DS();
            for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
            {
                DataRow items = ds.Tables[0].Rows[cuIndex];
                model = new DARDOC_DS();
                model._Docno = items["DARNO"].ToString().ToUpper();
                model._Empno = items["EMPNO"].ToString().ToUpper();
                model._Empname = items["EMPNAME"].ToString().ToUpper();
                model._Empemail = items["EMP_EMAIL"].ToString().ToUpper();
                model._Dep = items["DEPT"].ToString().ToUpper();
                model._Mgrname = items["MGRNAME"].ToString().ToUpper();
                model._Mgremail = items["MGR_EMAIL"].ToString().ToUpper();
                model._DOCType = items["DOCTYPE"].ToString().ToUpper();
                model._DOCTypenm = items["DOCNM"].ToString().ToUpper();
                model._OPTType = items["OPTTYPE"].ToString().ToUpper();
                model._OPTTypenm = items["OPTNM"].ToString().ToUpper();
                model._Filename = items["FILENAME"].ToString().ToUpper();
                model._FileTemp = items["TEMPFILE"].ToString().ToUpper();
                model._Text = items["TEXT"].ToString().ToUpper();
                list.Add(model);
            }
            return list;
        }

        public bool UPD_STATUS(ConnectionWrapper cnn, string status,string  darno)
        {

            try
            {
                StringBuilder comstr = new StringBuilder();
                comstr.AppendLine(" UPDATE ");
                comstr.AppendLine(WinNemoLib.Nemo.GetINI("Dbase", "Base") + ".[dbo].[DARDOC]");
                comstr.AppendLine("SET STATUS = Convert(int,Status)+ Convert(int,@status)");
                comstr.AppendLine(" Where DARNO=@darno ");


                    CommandWrapper cmd = cnn.CreateCommand();
                    cmd.CommandText = comstr.ToString();

                    cmd.AddParameterStringWithValue("@darno", darno);
                    cmd.AddParameterStringWithValue("@status", status);

                    if (cmd.ExecuteNonQuery() <= 0)
                    {
                        cnn.RollbackTransaction();
                        return false;
                    }
                

                return true;
            }
            catch (Exception ex)
            {
                return false;
                throw ex;
            }
        }

    }
}